using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using ThridGroup.ERP.Application.Contracts.DTO;
using ThridGroup.ERP.Application.Contracts.ProductionSystem;
using Volo.Abp.Application.Services;
using Volo.Abp.Domain.Repositories;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;
using ThridGroup.ERP.ProductionSystem;
using ThridGroup.ERP.DTO;
using Volo.Abp.Uow;

namespace ThridGroup.ERP.Application.ProductionSystem
{
    /// <summary>32
    /// 车间服务实现
    /// </summary>
    public class WorkshopService : ApplicationService, IWorkshopService
    {
        private readonly IRepository<Workshop, int> _workshopRepository;
        private readonly ILogger<WorkshopService> _logger;

        public WorkshopService(
            IRepository<Workshop, int> workshopRepository,
            ILogger<WorkshopService> logger)
        {
            _workshopRepository = workshopRepository;
            _logger = logger;
        }

        /// <summary>
        /// 添加车间
        /// </summary>
        [UnitOfWork]
        public async Task<int> AddWorkshop(CreateUpdateWorkshopDto input)
        {
            try
            {
                _logger.LogInformation($"开始添加车间：Code={input.Code}, Name={input.Name}");

                // 验证输入
                await ValidateWorkshopInputAsync(input);
                
                // 创建新实体
                var entity = new Workshop
                {
                    Code = input.Code?.Trim(),
                    Name = input.Name?.Trim(),
                    Description = input.Description?.Trim() ?? string.Empty,
                    WorkshopSize = input.WorkshopSize?.Trim() ?? string.Empty,
                    WorkshopPerson = input.WorkshopPerson?.Trim() ?? string.Empty,
                    IsEnable = input.IsEnable,
                    CreateTime = DateTime.Now
                };

                _logger.LogInformation($"准备插入数据库：Code={entity.Code}, Name={entity.Name}");
                
                try
                {
                    // 使用Repository的InsertAsync方法
                    var result = await _workshopRepository.InsertAsync(entity, true);
                    
                    if (result == null || result.Id <= 0)
                    {
                        throw new Exception("插入成功但未返回有效ID");
                    }
                    
                    _logger.LogInformation($"车间添加成功，ID：{result.Id}");
                    return result.Id;
                }
                catch (Exception dbEx)
                {
                    _logger.LogError(dbEx, $"数据库插入错误：{dbEx.Message}");
                    throw new Exception($"数据库操作失败：{dbEx.Message}", dbEx);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"添加车间失败：{ex.Message}");
                throw new Exception($"添加车间失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 更新车间
        /// </summary>
        [UnitOfWork]
        public async Task<int> UpdateWorkshop(int id, CreateUpdateWorkshopDto input)
        {
            try
            {
                _logger.LogInformation($"开始更新车间，ID：{id}, Code={input.Code}, Name={input.Name}");

                // 验证ID
                if (id <= 0)
                {
                    throw new ArgumentException("无效的车间ID");
                }

                // 验证输入
                await ValidateWorkshopInputAsync(input, id);

                try
                {
                    // 先获取现有实体
                    var entity = await _workshopRepository.GetAsync(id);
                    
                    if (entity == null)
                    {
                        throw new Exception($"未找到ID为{id}的车间");
                    }

                    // 更新实体属性
                    entity.Code = input.Code?.Trim();
                    entity.Name = input.Name?.Trim();
                    entity.Description = input.Description?.Trim() ?? string.Empty;
                    entity.WorkshopSize = input.WorkshopSize?.Trim() ?? string.Empty;
                    entity.WorkshopPerson = input.WorkshopPerson?.Trim() ?? string.Empty;
                    entity.IsEnable = input.IsEnable;

                    _logger.LogInformation($"准备更新数据库，ID：{entity.Id}");
                    
                    // 使用Repository的UpdateAsync方法
                    await _workshopRepository.UpdateAsync(entity, true);
                    
                    _logger.LogInformation($"车间更新成功，ID：{entity.Id}");
                    return entity.Id;
                }
                catch (Exception dbEx)
                {
                    _logger.LogError(dbEx, $"数据库更新错误：{dbEx.Message}");
                    throw new Exception($"数据库操作失败：{dbEx.Message}", dbEx);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"更新车间失败：{ex.Message}");
                throw new Exception($"更新车间失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除车间
        /// </summary>
        [UnitOfWork]
        public async Task DeleteWorkshop(int id)
        {
            try
            {
                _logger.LogInformation($"开始删除车间，ID：{id}");

                // 验证ID
                if (id <= 0)
                {
                    throw new ArgumentException("无效的车间ID");
                }

                try
                {
                    // 先获取现有实体
                    var entity = await _workshopRepository.GetAsync(id);
                    
                    if (entity == null)
                    {
                        throw new Exception($"未找到ID为{id}的车间");
                    }

                    // 进行物理删除
                    _logger.LogInformation($"准备删除数据库记录，ID：{id}");
                    await _workshopRepository.DeleteAsync(entity, true);
                    
                    _logger.LogInformation($"车间删除成功，ID：{id}");
                }
                catch (Exception dbEx)
                {
                    _logger.LogError(dbEx, $"数据库删除错误：{dbEx.Message}");
                    throw new Exception($"数据库操作失败：{dbEx.Message}", dbEx);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"删除车间失败：{ex.Message}");
                throw new Exception($"删除车间失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取单个车间
        /// </summary>
        public async Task<WorkshopDto> GetWorkshop(int id)
        {
            try
            {
                _logger.LogInformation($"开始获取车间，ID：{id}");

                // 验证ID
                if (id <= 0)
                {
                    throw new ArgumentException("无效的车间ID");
                }

                try
                {
                    // 获取实体
                    var entity = await _workshopRepository.GetAsync(id);
                    
                    if (entity == null)
                    {
                        throw new Exception($"未找到ID为{id}的车间");
                    }

                    var dto = MapToDto(entity);
                    
                    _logger.LogInformation($"成功获取车间，ID：{id}");
                    return dto;
                }
                catch (Exception dbEx)
                {
                    _logger.LogError(dbEx, $"数据库查询错误：{dbEx.Message}");
                    throw new Exception($"数据库操作失败：{dbEx.Message}", dbEx);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取车间失败：{ex.Message}");
                throw new Exception($"获取车间失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取所有车间
        /// </summary>
        public async Task<List<WorkshopDto>> GetWorkshops()
        {
            try
            {
                _logger.LogInformation("开始获取所有车间");

                try
                {
                    // 使用原生SQL查询避免EF Core的列名映射问题
                    var sql = $@"SELECT w.""Id"", w.""WorkshopCode"" as ""Code"", w.""WorkshopName"" as ""Name"", 
                               w.""Context"" as ""Description"", w.""IsEnable"", 
                               w.""WorkshopSize"", w.""WorkshopPerson""
                               FROM ""Workshops"" w
                               ORDER BY w.""WorkshopCode""";
                    
                    var dbContext = await _workshopRepository.GetDbContextAsync();
                    var connection = dbContext.Database.GetDbConnection();
                    if (connection.State != System.Data.ConnectionState.Open)
                    {
                        await connection.OpenAsync();
                    }
                    
                    var entities = new List<Workshop>();
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = sql.ToString();
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                var id = reader.GetInt32(reader.GetOrdinal("Id"));
                                var workshop = new Workshop(id);
                                workshop.Code = reader.GetString(reader.GetOrdinal("Code"));
                                workshop.Name = reader.GetString(reader.GetOrdinal("Name"));
                                workshop.Description = !reader.IsDBNull(reader.GetOrdinal("Description")) 
                                    ? reader.GetString(reader.GetOrdinal("Description")) : string.Empty;
                                workshop.IsEnable = reader.GetBoolean(reader.GetOrdinal("IsEnable"));
                                workshop.WorkshopSize = !reader.IsDBNull(reader.GetOrdinal("WorkshopSize")) 
                                    ? reader.GetString(reader.GetOrdinal("WorkshopSize")) : string.Empty;
                                workshop.WorkshopPerson = !reader.IsDBNull(reader.GetOrdinal("WorkshopPerson")) 
                                    ? reader.GetString(reader.GetOrdinal("WorkshopPerson")) : string.Empty;
                                workshop.CreateTime = DateTime.Now;
                                workshop.ParentId = 0;
                                workshop.ParentName = string.Empty;
                                workshop.Index = 0;
                                workshop.IsDeleted = false;
                                
                                entities.Add(workshop);
                            }
                        }
                    }
                    
                    var dtos = entities.Select(MapToDto).ToList();
                    
                    _logger.LogInformation($"成功获取所有车间，数量：{dtos.Count}");
                    return dtos;
                }
                catch (Exception dbEx)
                {
                    _logger.LogError(dbEx, $"数据库查询错误：{dbEx.Message}");
                    throw new Exception($"数据库操作失败：{dbEx.Message}", dbEx);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取所有车间失败：{ex.Message}");
                throw new Exception($"获取所有车间失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据条件查询车间
        /// </summary>
        public async Task<List<WorkshopDto>> GetWorkshopsByCondition(string code, string name, bool? isEnable)
        {
            try
            {
                _logger.LogInformation($"开始条件查询车间：Code={code}, Name={name}, IsEnable={isEnable}");

                try
                {
                    // 使用原生SQL查询避免EF Core的列名映射问题
                    // 构建带条件的SQL查询
                    var whereClause = new List<string>();
                    if (!string.IsNullOrWhiteSpace(code))
                    {
                        whereClause.Add($"w.\"WorkshopCode\" LIKE '%{code}%'");
                    }
                    
                    if (!string.IsNullOrWhiteSpace(name))
                    {
                        whereClause.Add($"w.\"WorkshopName\" LIKE '%{name}%'");
                    }
                    
                    if (isEnable.HasValue)
                    {
                        whereClause.Add($"w.\"IsEnable\" = {(isEnable.Value ? "true" : "false")}");
                    }
                    
                    var whereClauseStr = whereClause.Count > 0 
                        ? "WHERE " + string.Join(" AND ", whereClause) 
                        : string.Empty;
                    
                    var sql = $@"SELECT w.""Id"", w.""WorkshopCode"" as ""Code"", w.""WorkshopName"" as ""Name"", 
                               w.""Context"" as ""Description"", w.""IsEnable"", 
                               w.""WorkshopSize"", w.""WorkshopPerson""
                               FROM ""Workshops"" w
                               {whereClauseStr}
                               ORDER BY w.""WorkshopCode""";
                    
                    var dbContext = await _workshopRepository.GetDbContextAsync();
                    var connection = dbContext.Database.GetDbConnection();
                    if (connection.State != System.Data.ConnectionState.Open)
                    {
                        await connection.OpenAsync();
                    }
                    
                    var entities = new List<Workshop>();
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = sql.ToString();
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                var id = reader.GetInt32(reader.GetOrdinal("Id"));
                                var workshop = new Workshop(id);
                                workshop.Code = reader.GetString(reader.GetOrdinal("Code"));
                                workshop.Name = reader.GetString(reader.GetOrdinal("Name"));
                                workshop.Description = !reader.IsDBNull(reader.GetOrdinal("Description")) 
                                    ? reader.GetString(reader.GetOrdinal("Description")) : string.Empty;
                                workshop.IsEnable = reader.GetBoolean(reader.GetOrdinal("IsEnable"));
                                workshop.WorkshopSize = !reader.IsDBNull(reader.GetOrdinal("WorkshopSize")) 
                                    ? reader.GetString(reader.GetOrdinal("WorkshopSize")) : string.Empty;
                                workshop.WorkshopPerson = !reader.IsDBNull(reader.GetOrdinal("WorkshopPerson")) 
                                    ? reader.GetString(reader.GetOrdinal("WorkshopPerson")) : string.Empty;
                                workshop.CreateTime = DateTime.Now;
                                workshop.ParentId = 0;
                                workshop.ParentName = string.Empty;
                                workshop.Index = 0;
                                workshop.IsDeleted = false;
                                
                                entities.Add(workshop);
                            }
                        }
                    }
                    
                    var dtos = entities.Select(MapToDto).ToList();
                    
                    _logger.LogInformation($"条件查询车间成功，数量：{dtos.Count}");
                    return dtos;
                }
                catch (Exception dbEx)
                {
                    _logger.LogError(dbEx, $"数据库查询错误：{dbEx.Message}");
                    throw new Exception($"数据库操作失败：{dbEx.Message}", dbEx);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"条件查询车间失败：{ex.Message}");
                throw new Exception($"条件查询车间失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 检查车间编码是否存在
        /// </summary>
        public async Task<bool> IsCodeExist(string code, int? excludeId = null)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(code))
                {
                    return false;
                }

                // 使用原生SQL查询避免EF Core的列名映射问题
                var whereClause = new List<string>();
                whereClause.Add($"w.\"WorkshopCode\" = '{code}'");
                
                if (excludeId.HasValue)
                {
                    whereClause.Add($"w.\"Id\" != {excludeId.Value}");
                }
                
                var whereClauseStr = string.Join(" AND ", whereClause);
                
                var sql = $@"SELECT COUNT(*) FROM ""Workshops"" w WHERE {whereClauseStr}";
                
                var dbContext = await _workshopRepository.GetDbContextAsync();
                var connection = dbContext.Database.GetDbConnection();
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    await connection.OpenAsync();
                }
                
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql.ToString();
                    var count = Convert.ToInt32(await command.ExecuteScalarAsync());
                    return count > 0;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"检查车间编码是否存在时出错：{ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 启用或禁用车间
        /// </summary>
        [UnitOfWork]
        public async Task<bool> SetWorkshopStatus(int id, bool isEnable)
        {
            try
            {
                _logger.LogInformation($"开始设置车间状态，ID：{id}, IsEnable：{isEnable}");

                // 获取车间实体
                var entity = await _workshopRepository.GetAsync(id);
                if (entity == null)
                {
                    throw new Exception($"未找到ID为{id}的车间");
                }

                // 更新状态
                entity.IsEnable = isEnable;
                await _workshopRepository.UpdateAsync(entity, true);

                _logger.LogInformation($"车间状态设置成功，ID：{id}, IsEnable：{isEnable}");
                return true;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"设置车间状态失败：{ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 验证车间输入
        /// </summary>
        private async Task ValidateWorkshopInputAsync(CreateUpdateWorkshopDto input, int? id = null)
        {
            // 必填字段验证
            if (string.IsNullOrWhiteSpace(input.Code))
            {
                throw new ArgumentException("车间编码不能为空");
            }

            if (string.IsNullOrWhiteSpace(input.Name))
            {
                throw new ArgumentException("车间名称不能为空");
            }

            // 验证编码唯一性
            bool isCodeExist = await IsCodeExist(input.Code, id);
            if (isCodeExist)
            {
                throw new ArgumentException($"车间编码 '{input.Code}' 已存在");
            }
        }

        /// <summary>
        /// 实体映射到DTO
        /// </summary>
        private WorkshopDto MapToDto(Workshop entity)
        {
            if (entity == null)
            {
                return null;
            }

            return new WorkshopDto
            {
                Id = entity.Id,
                Code = entity.Code,
                Name = entity.Name,
                Description = entity.Description,
                IsEnable = entity.IsEnable,
                WorkshopSize = entity.WorkshopSize,
                WorkshopPerson = entity.WorkshopPerson,
                CreateTime = entity.CreateTime,
                Children = entity.Children?.Select(MapToDto).ToList() ?? new List<WorkshopDto>()
            };
        }

        /// <summary>
        /// 获取车间树形结构
        /// </summary>
        public async Task<List<WorkshopDto>> GetWorkshopTree()
        {
            try
            {
                _logger.LogInformation("开始获取车间树形结构");

                try
                {
                    // 使用原生SQL查询避免EF Core的列名映射问题
                    var sql = $@"SELECT w.""Id"", w.""WorkshopCode"" as ""Code"", w.""WorkshopName"" as ""Name"", 
                               w.""Context"" as ""Description"", w.""IsEnable"", 
                               w.""WorkshopSize"", w.""WorkshopPerson""
                               FROM ""Workshops"" w
                               ORDER BY w.""WorkshopCode""";
                    
                    var dbContext = await _workshopRepository.GetDbContextAsync();
                    var connection = dbContext.Database.GetDbConnection();
                    if (connection.State != System.Data.ConnectionState.Open)
                    {
                        await connection.OpenAsync();
                    }
                    
                    var allWorkshops = new List<Workshop>();
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = sql.ToString();
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                var id = reader.GetInt32(reader.GetOrdinal("Id"));
                                var workshop = new Workshop(id);
                                workshop.Code = reader.GetString(reader.GetOrdinal("Code"));
                                workshop.Name = reader.GetString(reader.GetOrdinal("Name"));
                                workshop.Description = !reader.IsDBNull(reader.GetOrdinal("Description")) 
                                    ? reader.GetString(reader.GetOrdinal("Description")) : string.Empty;
                                workshop.IsEnable = reader.GetBoolean(reader.GetOrdinal("IsEnable"));
                                workshop.WorkshopSize = !reader.IsDBNull(reader.GetOrdinal("WorkshopSize")) 
                                    ? reader.GetString(reader.GetOrdinal("WorkshopSize")) : string.Empty;
                                workshop.WorkshopPerson = !reader.IsDBNull(reader.GetOrdinal("WorkshopPerson")) 
                                    ? reader.GetString(reader.GetOrdinal("WorkshopPerson")) : string.Empty;
                                workshop.CreateTime = DateTime.Now;
                                workshop.ParentId = 0;
                                workshop.ParentName = string.Empty;
                                workshop.Index = 0;
                                workshop.IsDeleted = false;
                                
                                allWorkshops.Add(workshop);
                            }
                        }
                    }
                    
                    // 转换为DTO
                    var allDtos = allWorkshops.Select(MapToDto).ToList();
                    
                    // 构建树形结构
                    var rootNodes = new List<WorkshopDto>();
                    
                    // 首先找出所有根节点（ParentId为0的节点）
                    foreach (var dto in allDtos.Where(x => x.ParentId == 0))
                    {
                        rootNodes.Add(dto);
                        // 递归查找子节点
                        BuildWorkshopTree(dto, allDtos);
                    }
                    
                    _logger.LogInformation($"车间树形结构获取成功，根节点数量：{rootNodes.Count}");
                    return rootNodes;
                }
                catch (Exception dbEx)
                {
                    _logger.LogError(dbEx, $"获取车间树形结构时数据库操作失败：{dbEx.Message}");
                    throw new Exception($"数据库操作失败：{dbEx.Message}", dbEx);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取车间树形结构失败：{ex.Message}");
                throw new Exception($"获取车间树形结构失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 递归构建车间树
        /// </summary>
        private void BuildWorkshopTree(WorkshopDto parent, List<WorkshopDto> allWorkshops)
        {
            // 查找所有ParentId等于当前节点Id的节点，这些是当前节点的子节点
            var children = allWorkshops.Where(x => x.ParentId == parent.Id).ToList();
            
            // 将子节点添加到当前节点的Children集合中
            foreach (var child in children)
            {
                parent.Children.Add(child);
                // 递归查找子节点的子节点
                BuildWorkshopTree(child, allWorkshops);
            }
        }
    }
} 